package day1202friut;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import day1130.JDBCUtil;

public class BillDao {

	// 插入数据
	public static void insert(Billmodel1 bill) {
		String sql = "insert into billrecord(name,soldnumber,soldtime,totalprice,costprice) values(?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, bill.getName());
			ps.setInt(2, bill.getSoldnumber());
			ps.setString(3, bill.getSoldtime());
			ps.setDouble(4, bill.getTotalprice());
			ps.setDouble(5, bill.getCostprice());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
	}

	// 查找账单
	public static ArrayList<Billmodel1> select(String stime, String etime) {
		ArrayList<Billmodel1> list = new ArrayList<Billmodel1>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet result = null;
		StringBuffer sql = new StringBuffer("select * from billrecord");
		try {
			conn = JDBCUtil.getConnection();
			if (stime != null && etime != null) {
				sql.append(" where soldtime>? and soldtime<?");
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, stime);
				ps.setString(2, etime);
			}else {
				ps = conn.prepareStatement(sql.toString());
			}
			result = ps.executeQuery();
			while (result.next()) {
				Billmodel1 bill = new Billmodel1();
				bill.setCostprice(result.getDouble("costprice"));
				bill.setName(result.getString("name"));
				bill.setSoldnumber(result.getInt("soldnumber"));
				bill.setSoldtime(result.getString("soldtime"));
				bill.setTotalprice(result.getDouble("totalprice"));
				list.add(bill);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

}
